#
# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 2 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program.  If not, see <http://www.gnu.org/licenses/>.
#
# Copyright (c) 2010 by Derek Anderson
#

import datetime, json, os, sqlite3, sys, textwrap, threading, time, traceback, uuid

import sqlparse


try:
  import pygtk
  pygtk.require("2.0")
  import gtk
  import gtk.glade
  import gtksourceview2
  import gobject, gnome
  import pango
except:
  traceback.print_exc()
  sys.exit(1)

from util import *
import gui_newconn



class DeSQL(object):


  def __init__(self):

    init_db()

    self.xml = gtk.glade.XML( os.path.join( RUN_FROM_DIR, 'desql.glade' ) )
    self.window = self.xml.get_widget('window1')
    self.window.set_title(PROGRAM_NAME)
    self.window.connect("delete-event", lambda a,b: self.close() )
    self.window.connect("window-state-event", self.handle_window_state_event )
    self.window.set_icon_from_file(os.path.join(RUN_FROM_DIR,'icon_256.png'))
    accel_group = gtk.AccelGroup()
    accel_group.connect_group(ord('q'), gtk.gdk.CONTROL_MASK, gtk.ACCEL_LOCKED, lambda a,b,c,d: self.close())
    accel_group.connect_group(ord('r'), gtk.gdk.CONTROL_MASK, gtk.ACCEL_LOCKED, lambda a,b,c,d: self.execute())
    accel_group.connect_group(ord('f'), gtk.gdk.CONTROL_MASK, gtk.ACCEL_LOCKED, lambda a,b,c,d: self.format())
    accel_group.connect_group(ord('w'), gtk.gdk.CONTROL_MASK, gtk.ACCEL_LOCKED, lambda a,b,c,d: self.close_current_tab())
    accel_group.connect_group(ord('n'), gtk.gdk.CONTROL_MASK, gtk.ACCEL_LOCKED, lambda a,b,c,d: self.new_conn())
    self.window.add_accel_group(accel_group)
    self.conns = []
    
    self.xml.get_widget('button_open_connection').connect('clicked', lambda x: self.new_conn() )
    self.xml.get_widget('menuitem_new_conn').connect('activate', lambda x: self.new_conn() )
    self.xml.get_widget('menuitem_quit').connect('activate', lambda x: self.close() )
    self.xml.get_widget('menuitem_about').connect('activate', lambda x: self.show_about() )
    
    self.notebook = self.xml.get_widget('notebook1')
    self.notebook.set_visible(False)
    self.no_conns_bar = self.xml.get_widget('alignment_open_connection')
    
    self.xml.get_widget('menuitem_db_pos_top').connect('activate', self.handle_menuitem_db_pos_event, 'TOP' )
    self.xml.get_widget('menuitem_db_pos_left').connect('activate', self.handle_menuitem_db_pos_event, 'LEFT' )
    self.xml.get_widget('menuitem_db_pos_bottom').connect('activate', self.handle_menuitem_db_pos_event, 'BOTTOM' )
    self.xml.get_widget('menuitem_db_pos_right').connect('activate', self.handle_menuitem_db_pos_event, 'RIGHT' )

    self.handle_menuitem_db_pos_event(None,get_metadata(NAMESPACE_DESQL,'db_tab_position','LEFT'))

    if get_metadata(NAMESPACE_DESQL,'maximized'):
      self.window.maximize()
    
    self.window.show()


  def close(self):
    self.window.hide()
    check_and_prompt_for_vacuum()
    gtk.main_quit()
  
  
  def show_about(self):
    about = gtk.AboutDialog()
    about.set_name(PROGRAM_NAME)
    about.set_version(PROGRAM_VERSION)
    about.set_logo(gtk.gdk.pixbuf_new_from_file_at_size(os.path.join(RUN_FROM_DIR,'icon_256.png'),64,64))
    about.set_copyright('(C) 2010 - Derek Anderson')
    about.set_website('http://code.google.com/p/desql/')
    with open(os.path.join(RUN_FROM_DIR,'gpl-2.0.txt')) as f:
      about.set_license(f.read())
    about.connect('response',lambda x: about.destroy())
    about.connect('close',lambda x: about.destroy())
    about.connect('delete-event',lambda x: about.destroy())
    about.run()
    about.destroy()
    
    
  def handle_window_state_event(self,widget,event):
    if event.new_window_state & gtk.gdk.WINDOW_STATE_MAXIMIZED:
      set_metadata(NAMESPACE_DESQL,'maximized',True)
    else:
      set_metadata(NAMESPACE_DESQL,'maximized',False)
    

  def handle_menuitem_db_pos_event(self,widget,position):
    if position=='TOP': self.notebook.set_tab_pos(gtk.POS_TOP)
    if position=='LEFT': self.notebook.set_tab_pos(gtk.POS_LEFT)
    if position=='BOTTOM': self.notebook.set_tab_pos(gtk.POS_BOTTOM)
    if position=='RIGHT': self.notebook.set_tab_pos(gtk.POS_RIGHT)
    set_metadata(NAMESPACE_DESQL,'db_tab_position',position)

  def execute(self):
    page = self.notebook.get_nth_page( self.notebook.get_current_page() )
    for conn in self.conns:
      if conn.node()==page:
        conn.execute_entry()
        break
      
    
  def format(self):
    page = self.notebook.get_nth_page( self.notebook.get_current_page() )
    for conn in self.conns:
      if conn.node()==page:
        conn.format()
        break
      
    
  def close_current_tab(self):
    self.notebook.remove_page( self.notebook.get_current_page() )


  def close_tab(self, tab):
    self.notebook.remove_page(self.notebook.page_num(tab))
    self.no_conns_bar.set_visible(len(self.conns)==0)
    self.notebook.set_visible(len(self.conns)!=0)
    
  def new_conn(self):
    gui_newconn.NewConn(self.notebook, self)


  def get_open_conn_ids(self):
    return set([ conn.conn_id for conn in self.conns ])

  def get_metadata(self,x,conn_id,schema,table):
    print conn_id,schema,table
    matching_conns = [ conn for conn in self.conns if conn.conn_id==conn_id ]
    if not matching_conns:
      print 'no match for conn_id', conn_id
      return
    conn = matching_conns[0]
    print 'conn', conn
    print conn.metadata[schema][table]
    return conn.metadata[schema][table]

  def get_mapping(self, conn_id, schema, table, col, replace_col, values):
    matching_conns = [ conn for conn in self.conns if conn.conn_id==conn_id ]
    if not matching_conns:
      print 'no match for conn_id', conn_id
      return
    conn = matching_conns[0]
    print 'conn', conn
    actual_conn = conn.get_conn()
    cur = actual_conn.cursor()
    values_s = ','.join([ "'%s'" % s.replace("'",'') for s in values ])
    sql = "select %s, %s from %s%s%s where %s in (%s)" % (col, replace_col,schema,conn.schema_tbl_sep,table,col,values_s)
    print 'sql', sql
    cur.execute(sql)
    val_map = {}
    for row in cur:
      val_map[str(row[0])] = row[1]
    return val_map

  def lookup(self,x,conn_id,schema,table,column,value):
    print conn_id,schema,table,column,value
    matching_conns = [ conn for conn in self.conns if conn.conn_id==conn_id ]
    if not matching_conns:
      print 'no match for conn_id', conn_id
      return
    conn = matching_conns[0]
    print 'conn', conn
    actual_conn = conn.get_conn()
    cur = actual_conn.cursor()
    sql = "select * from %s%s%s where %s='%s'" % (schema,conn.schema_tbl_sep,table,column,value.replace("'",''))
    print 'sql', sql
    cur.execute(sql)
    for row in cur:
      headers = [ col[0] for col in cur.description ]
      data = list(row)
      combined = zip(headers,data)
      statements = [ '<i>%s:</i> %s' % (pango_escape(h),pango_escape(d)) for h,d in combined ]
      s = '\n'.join(statements)
      print s
      dialog = gtk.MessageDialog(parent=None, flags=0, type=gtk.MESSAGE_INFO, buttons=gtk.BUTTONS_OK, message_format=None)
      dialog.set_markup('From <b>%s%s%s</b>:\n'%(schema,conn.schema_tbl_sep,table) +s)
      dialog.connect('delete-event', lambda x,y,d: d.destroy(), dialog )
      dialog.connect('response', lambda x,y,d: d.destroy(), dialog )
      dialog.show_all()
      break
    else:
      dialog = gtk.MessageDialog(parent=None, flags=0, type=gtk.MESSAGE_INFO, buttons=gtk.BUTTONS_OK, message_format=None)
      dialog.set_markup('not found')
      dialog.connect('delete-event', lambda x,y,d: d.destroy(), dialog )
      dialog.connect('response', lambda x,y,d: d.destroy(), dialog )
      dialog.show_all()
    cur.close()

